﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading;
using System.Web.Http;
using Waiyun.WebApi.Data;

namespace Waiyun.WebApi
{
    public class S3438Controller : ApiController
    {
        public static string ConnectionString;

        static S3438Controller()
        {
            ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["weitsm_stgccqy2"].ConnectionString;
        }

        [HttpGet]
        public SingleResult<S3438GlobalTVC> GlobalTVC()
        {
            return ThreadPoolTool.RunSingle<S3438GlobalTVC>("S3438\\GlobalTVC", ConnectionString, (connection) =>
            {
                long t = 0;
                long v = 0;
                long c = 0;
                using (OracleCommand command = new OracleCommand(@"SELECT 
T.WH_NAME name,
SUM(T.IN_TON) t,
SUM(T.IN_VOL) v,
SUM(T.IN_QTY) c 
FROM PRD_STG_CC.BUS_WH_IN_OUT T
WHERE T.D_STAT_DATE BETWEEN TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE)
GROUP BY T.WH_NAME", connection))
                {
                    using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                    {
                        DataTable table = new DataTable();
                        adapter.Fill(table);

                        for (int index = 0; index < table.Rows.Count; index++)
                        {
                            t += Convert.ToInt64(table.Rows[index]["t"]);
                            v += Convert.ToInt64(table.Rows[index]["v"]);
                            c += Convert.ToInt64(table.Rows[index]["c"]);
                        }
                        return new S3438GlobalTVC()
                        {
                            T = t,
                            V = v,
                            C = c
                        };
                    }
                }
            });
        }

        [HttpGet]
        public SingleResult<S3438GlobalTVC> GlobalTVCIN()
        {
            return ThreadPoolTool.RunSingle<S3438GlobalTVC>("S3438\\GlobalTVCIN", ConnectionString, (connection) =>
            {
                long t = 0;
                long v = 0;
                long c = 0;
                using (OracleCommand command = new OracleCommand(@"SELECT 
T.WH_NAME name,
SUM(T.IN_TON) t,
SUM(T.IN_VOL) v,
SUM(T.IN_QTY) c 
FROM PRD_STG_CC.BUS_WH_IN_OUT T
WHERE T.D_STAT_DATE BETWEEN TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE)
GROUP BY T.WH_NAME", connection))
                {
                    using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                    {
                        DataTable table = new DataTable();
                        adapter.Fill(table);

                        for (int index = 0; index < table.Rows.Count; index++)
                        {
                            t += Convert.ToInt64(table.Rows[index]["t"]);
                            v += Convert.ToInt64(table.Rows[index]["v"]);
                            c += Convert.ToInt64(table.Rows[index]["c"]);
                        }
                        return new S3438GlobalTVC()
                        {
                            T = t,
                            V = v,
                            C = c
                        };
                    }
                }
            });
        }

        [HttpGet]
        public SingleResult<S3438GlobalTVC> GlobalTVCOUT()
        {
            return ThreadPoolTool.RunSingle<S3438GlobalTVC>("S3438\\GlobalTVCOUT", ConnectionString, (connection) =>
            {
                long t = 0;
                long v = 0;
                long c = 0;
                using (OracleCommand command = new OracleCommand(@"SELECT 
T.WH_NAME name,
SUM(T.OUT_TON) t,
SUM(T.OUT_VOL) v,
SUM(T.OUT_QTY) c 
FROM PRD_STG_CC.BUS_WH_IN_OUT T
WHERE T.D_STAT_DATE BETWEEN TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE)
GROUP BY T.WH_NAME", connection))
                {
                    using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                    {
                        DataTable table = new DataTable();
                        adapter.Fill(table);

                        for (int index = 0; index < table.Rows.Count; index++)
                        {
                            t += Convert.ToInt64(table.Rows[index]["t"]);
                            v += Convert.ToInt64(table.Rows[index]["v"]);
                            c += Convert.ToInt64(table.Rows[index]["c"]);
                        }
                        return new S3438GlobalTVC()
                        {
                            T = t,
                            V = v,
                            C = c
                        };
                    }
                }
            });
        }

        [HttpGet]
        public SingleResult<S3438WH> WH(int id)
        {
            string lastWeekSql = string.Format(@"SELECT * FROM 
(
SELECT 
T.WH_ID id,
T.WH_NAME name,
SUM(T.OUT_TON) ton,
SUM(T.OUT_VOL) vol,
SUM(T.OUT_QTY) qty,
ROW_NUMBER()OVER(ORDER BY SUM(T.OUT_TON) DESC) RANK_NUM
FROM PRD_STG_CC.BUS_WH_IN_OUT T
WHERE T.D_STAT_DATE BETWEEN TRUNC(SYSDATE)-14 AND TRUNC(SYSDATE)-7
  AND T.WH_ID  ='{0}'
GROUP BY T.WH_ID,T.WH_NAME
) X
WHERE X.RANK_NUM <= 50", id);

            string curWeekSql = string.Format(@"SELECT * FROM 
(
SELECT 
T.WH_ID id,
T.WH_NAME name,
SUM(T.IN_TON) ton,
SUM(T.IN_VOL) vol,
SUM(T.IN_QTY) qty,
ROW_NUMBER()OVER(ORDER BY SUM(T.IN_TON) DESC) RANK_NUM
FROM PRD_STG_CC.BUS_WH_IN_OUT T
WHERE T.D_STAT_DATE BETWEEN TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE)
  AND T.WH_ID  ='{0}'
GROUP BY T.WH_ID,T.WH_NAME
) X
WHERE X.RANK_NUM <= 50", id);

            return ThreadPoolTool.RunSingle<S3438WH>("S3438\\WH", ConnectionString, (connection) =>
            {
                S3438WH wh = new S3438WH();
                using (OracleCommand command = new OracleCommand(lastWeekSql, connection))
                {
                    using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                    {
                        DataTable table = new DataTable();
                        adapter.Fill(table);

                        if (table.Rows.Count > 0)
                        {
                            wh.LastTon = Convert.ToInt64(table.Rows[0]["ton"]);
                            wh.LastVol = Convert.ToInt64(table.Rows[0]["vol"]);
                            wh.LastQty = Convert.ToInt64(table.Rows[0]["qty"]);
                        }
                    }
                }
                using (OracleCommand command = new OracleCommand(curWeekSql, connection))
                {
                    using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                    {
                        DataTable table = new DataTable();
                        adapter.Fill(table);

                        if (table.Rows.Count > 0)
                        {
                            wh.CurTon = Convert.ToInt64(table.Rows[0]["ton"]);
                            wh.CurVol = Convert.ToInt64(table.Rows[0]["vol"]);
                            wh.CurQty = Convert.ToInt64(table.Rows[0]["qty"]);
                        }
                    }
                }
                return wh;
            });
        }

        [HttpGet]
        public SingleResult<S3438WH> WHIN(int id)
        {
            string lastWeekSql = string.Format(@"SELECT * FROM 
(
SELECT 
T.WH_ID id,
T.WH_NAME name,
SUM(T.OUT_TON) ton,
SUM(T.OUT_VOL) vol,
SUM(T.OUT_QTY) qty,
ROW_NUMBER()OVER(ORDER BY SUM(T.OUT_TON) DESC) RANK_NUM
FROM PRD_STG_CC.BUS_WH_IN_OUT T
WHERE T.D_STAT_DATE BETWEEN TRUNC(SYSDATE)-14 AND TRUNC(SYSDATE)-7
  AND T.WH_ID   ='{0}'
GROUP BY T.WH_ID,T.WH_NAME
) X
WHERE X.RANK_NUM <= 50", id);

            string curWeekSql = string.Format(@"SELECT * FROM 
(
SELECT 
T.WH_ID id,
T.WH_NAME name,
SUM(T.IN_TON) ton,
SUM(T.IN_VOL) vol,
SUM(T.IN_QTY) qty,
ROW_NUMBER()OVER(ORDER BY SUM(T.IN_TON) DESC) RANK_NUM
FROM PRD_STG_CC.BUS_WH_IN_OUT T
WHERE T.D_STAT_DATE BETWEEN TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE)
  AND T.WH_ID  ='{0}'
GROUP BY T.WH_ID,T.WH_NAME
) X
WHERE X.RANK_NUM <= 50", id);

            return ThreadPoolTool.RunSingle<S3438WH>(string.Format("S3438\\WHIN{0}", id), ConnectionString, (connection) =>
            {
                S3438WH wh = new S3438WH();
                using (OracleCommand command = new OracleCommand(lastWeekSql, connection))
                {
                    using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                    {
                        DataTable table = new DataTable();
                        adapter.Fill(table);

                        if (table.Rows.Count > 0)
                        {
                            wh.LastTon = Convert.ToInt64(table.Rows[0]["ton"]);
                            wh.LastVol = Convert.ToInt64(table.Rows[0]["vol"]);
                            wh.LastQty = Convert.ToInt64(table.Rows[0]["qty"]);
                        }
                    }
                }
                using (OracleCommand command = new OracleCommand(curWeekSql, connection))
                {
                    using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                    {
                        DataTable table = new DataTable();
                        adapter.Fill(table);

                        if (table.Rows.Count > 0)
                        {
                            wh.CurTon = Convert.ToInt64(table.Rows[0]["ton"]);
                            wh.CurVol = Convert.ToInt64(table.Rows[0]["vol"]);
                            wh.CurQty = Convert.ToInt64(table.Rows[0]["qty"]);
                        }
                    }
                }
                return wh;
            });
        }

        [HttpGet]
        public SingleResult<S3438WH> WHOUT(int id)
        {
            string lastWeekSql = string.Format(@"SELECT * FROM 
(
SELECT 
T.WH_ID id,
T.WH_NAME name,
SUM(T.OUT_TON) ton,
SUM(T.OUT_VOL) vol,
SUM(T.OUT_QTY) qty,
ROW_NUMBER()OVER(ORDER BY SUM(T.OUT_TON) DESC) RANK_NUM
FROM PRD_STG_CC.BUS_WH_IN_OUT T
WHERE T.D_STAT_DATE BETWEEN TRUNC(SYSDATE)-14 AND TRUNC(SYSDATE)-7
  AND T.WH_ID   ='{0}'
GROUP BY T.WH_ID,T.WH_NAME
) X
WHERE X.RANK_NUM <= 50", id);

            string curWeekSql = string.Format(@"SELECT * FROM 
(
SELECT 
T.WH_ID id,
T.WH_NAME name,
SUM(T.OUT_TON) ton,
SUM(T.OUT_VOL) vol,
SUM(T.OUT_QTY) qty,
ROW_NUMBER()OVER(ORDER BY SUM(T.OUT_TON) DESC) RANK_NUM
FROM PRD_STG_CC.BUS_WH_IN_OUT T
WHERE T.D_STAT_DATE BETWEEN TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE)
  AND T.WH_ID ='{0}'
GROUP BY T.WH_ID,T.WH_NAME
) X
WHERE X.RANK_NUM <= 50", id);

            return ThreadPoolTool.RunSingle<S3438WH>(string.Format("S3438\\WHOUT{0}", id), ConnectionString, (connection) =>
            {
                S3438WH wh = new S3438WH();
                using (OracleCommand command = new OracleCommand(lastWeekSql, connection))
                {
                    using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                    {
                        DataTable table = new DataTable();
                        adapter.Fill(table);

                        if (table.Rows.Count > 0)
                        {
                            wh.LastTon = Convert.ToInt64(table.Rows[0]["ton"]);
                            wh.LastVol = Convert.ToInt64(table.Rows[0]["vol"]);
                            wh.LastQty = Convert.ToInt64(table.Rows[0]["qty"]);
                        }
                    }
                }
                using (OracleCommand command = new OracleCommand(curWeekSql, connection))
                {
                    using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                    {
                        DataTable table = new DataTable();
                        adapter.Fill(table);

                        if (table.Rows.Count > 0)
                        {
                            wh.CurTon = Convert.ToInt64(table.Rows[0]["ton"]);
                            wh.CurVol = Convert.ToInt64(table.Rows[0]["vol"]);
                            wh.CurQty = Convert.ToInt64(table.Rows[0]["qty"]);
                        }
                    }
                }
                return wh;
            });
        }


        /// <summary>
        /// 库存统计重量
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public SingleResult<long> kctj_ton()
        {
            string name = "s3438\\kctj_ton";
            if (ThreadPools.Get(name) == null)
            {
                Thread thread = new Thread(() =>
                {
                    string errorMessage = null;
                    OracleConnection connection = ConnectionPool.GetConnection(ConnectionString, out errorMessage);
                    if (connection == null)
                    {
                        return;
                    }
                    try
                    {
                        long value = 0;
                        using (OracleCommand command = new OracleCommand(@"SELECT 
SUM(T.STO_TON) as ton
FROM PRD_STG_CC.BUS_WH_STORGE T
WHERE T.D_STAT_DATE BETWEEN TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE)
GROUP BY T.WH_NAME", connection))
                        {
                            using (OracleDataReader reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    value += Convert.ToInt64(reader[0]);
                                }
                                reader.Close();
                                Cache2.Set(name, value);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Logger.ErrorToTag("WebApi", string.Format("获取数据kctj_ton出错,ex:{0}", ex));
                        return;
                    }
                    finally
                    {
                        ConnectionPool.Release(connection);
                    }
                    ThreadPools.Stop(name);
                });
                thread.Name = name;
                ThreadPools.Start(thread);
            }
            object cacheValue = Cache2.Get(name);
            if (cacheValue != null)
            {
                return new SingleResult<long>(Convert.ToInt64(cacheValue));
            }
            return new SingleResult<long>(0);
        }

        [HttpGet]
        public SingleResult<long> kctj_vol()
        {
            string name = "s3438\\kctj_vol";
            if (ThreadPools.Get(name) == null)
            {
                Thread thread = new Thread(() =>
                {
                    string errorMessage = null;
                    OracleConnection connection = ConnectionPool.GetConnection(ConnectionString, out errorMessage);
                    if (connection == null)
                    {
                        return;
                    }
                    try
                    {
                        long value = 0;
                        using (OracleCommand command = new OracleCommand(@"SELECT 
SUM(T.STO_VOL) as vol
FROM PRD_STG_CC.BUS_WH_STORGE T
WHERE T.D_STAT_DATE BETWEEN TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE)
GROUP BY T.WH_NAME", connection))
                        {
                            using (OracleDataReader reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    value += Convert.ToInt64(reader[0]);
                                }
                                reader.Close();
                                Cache2.Set(name, value);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Logger.ErrorToTag("WebApi", string.Format("获取数据kctj_vol出错,ex:{0}", ex));
                        return;
                    }
                    finally
                    {
                        ConnectionPool.Release(connection);
                    }
                    ThreadPools.Stop(name);
                });
                thread.Name = name;
                ThreadPools.Start(thread);
            }
            object cacheValue = Cache2.Get(name);
            if (cacheValue != null)
            {
                return new SingleResult<long>(Convert.ToInt64(cacheValue));
            }
            return new SingleResult<long>(0);
        }
    }
}
